import os,time
import pymysql
import mysql.connector.pooling
import win32serviceutil
import shutil
import subprocess
from pathlib import Path
from outexcel import write_excel

sName ="xysql"
ppid = 0
config = {
    "host": "127.0.0.1",
    "port": "4567",
    "user": "sa",
    "password": "temp_password",
    "database": "mmaster",
    "pool_reset_session": True,
    # "pool_size": 5,
    # "autocommit": True,
    # "max_connections":20
    # "host": "192.168.1.38",
    # "password": "Coolrun741012",
    "ssl_disabled": True,
}

def read_sn(pool, sid):
    """查询商品条码"""
    x = None
    # pool = mysql.connector.pooling.MySQLConnectionPool(**config)
    with pool.get_connection() as conn:
        with conn.cursor() as cur:
            sql = "SELECT sn FROM goods_sn WHERE g_id=%s"
            cur.execute(sql, (sid,))
            x = cur.fetchone()
            cur.close()
    return x

def list_shop(pool):
    """商品列表"""
    x = None
    # pool = mysql.connector.pooling.MySQLConnectionPool(**config)
    with pool.get_connection() as conn:
        with conn.cursor() as cur:
            sql = '''
            SELECT 
                _name, (SELECT _name FROM goodscategory WHERE _id = _goodscategoryid), _price, _bar_stock, _warehouse_stock
            FROM market 
            '''
            cur.execute(sql)
            x = cur.fetchall()
            cur.close()
    return x
# ------------------------------------------------
def mysql_connect():
    """创建连接池"""
    pool = mysql.connector.pooling.MySQLConnectionPool(**config)
    return pool

def is_service_exist(service_name):
    """检测服务"""
    try:
        status = win32serviceutil.QueryServiceStatus(service_name)[1]
    except:
        return False
    else:
        return status

def do_service_stop(service_name):
    """停止服务"""
    try:
        win32serviceutil.StopService(service_name)
        return True
    except:
        return False
def do_service_start(service_name):
    """启动服务"""
    try:
        win32serviceutil.StartService(service_name)
        return True
    except:
        return False

def do_mysqldb_start(service_name):
    """启动debug模式"""
    def has_path(path):
        x =Path(path).parent
        return x
    try:
        r = win32serviceutil.LocateSpecificServiceExe(sName)
        r=r.split(' ')[0]
        # shutil.copy(os.path.join(has_path(has_path(r)), "my","my.ini"), os.path.join(has_path(has_path(r)), "my.ini"))
        # x = os.path.join(has_path(has_path(r)))
        cmds = f'{r} --skip-grant-tables --shared-memory'
        process = subprocess.Popen(cmds, shell=True)
    except BaseException as err:
        return False
    else:
        return process.pid

def stop_process_by_pid(pid):
    """结束进程"""
    try:
        # os.kill(pid, signal.SIGTERM)  # 发送SIGKILL信号
        # subprocess.call(["taskkill", "/F", "/PID",str(pid)])  # 调用kill命令来强制结束进程
        subprocess.call(["taskkill", "/F", "/IM", "xymysqld.exe"])
        print(f"Process with PID [{pid}] has been terminated.")
    except BaseException as e:
        print(f"结束进程 Error: {e}")

def create_mysqldb_user():
    """创建新用户"""
    try:
        conn = pymysql.connect( 
            host="127.0.0.1",
            port=4567,
            user="root",
            # password="",
            db="mysql",
            # charset='utf8mb4'
        )
        cursor = conn.cursor()
        # print("ok")
        sql1 = "CREATE USER 'sa'@'%' IDENTIFIED BY 'temp_password';"
        sql2 = "GRANT ALL PRIVILEGES ON *.* TO 'sa'@'%' WITH GRANT OPTION;"
        sql3 = "FLUSH PRIVILEGES;"
        cursor.execute(sql3)
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        conn.commit()
        cursor.close()
        conn.close()
    except BaseException as err:
        print(err)
        return False
    else:
        return True

def out_yao(logs=None):
    """开始导出"""
    logs.info("[小妖2] 开始导出商品数据.")
    try:
        # pool = mysql.connector.pooling.MySQLConnectionPool(**config)
        pool = mysql_connect()
    except BaseException as err:
        logs.error(f"[小妖2] sql {err}")
        pool = None
    if not pool:
        r = is_service_exist(sName)
        if not r:
            logs.error("[小妖2] 系统没有检测到相关组件,程序中止.")
            return False
        while True:
            r = is_service_exist(sName)
            if r == 4:
                x = do_service_stop(sName)
                if not x:
                    logs.error("[小妖2] 停止服务失败,程序中止.")
                    return False
            if r == 1:
                break
            time.sleep(2)
        pid = do_mysqldb_start(sName)
        if not r:
            logs.error("[小妖2] 启动DEBUG模式失败,程序中止.")
            return False
        time.sleep(2)
        r = create_mysqldb_user()
        if not r:
            logs.error("[小妖2] 创建DEBUG用户失败,程序中止.")
            return False
        time.sleep(2)
        r = stop_process_by_pid(pid)
        time.sleep(2)
        r = do_service_start(sName)
        if not r:
            logs.error("[小妖2] 启动数据服务失败,程序中止.")
            return False

        # 等待重启
        for i in range(9):
            logs.lastRow(f'{"="*i}')
            time.sleep(1)
        logs.lastRow(f'{"="*i}\n')

        # 重新创建连接池
        pool = mysql_connect()

        if not pool:
            logs.error("[小妖2] 连接数据服务失败,程序中止.")
            return False


    r = []
    for i in list_shop(pool):
        y = {"name":i[0],"tag":i[1], "cprice":"0","sprice":eval(str(i[2])),"stock":i[3] + i[4],"code":""}
        # x = read_sn(pool, i[5])
        # y["code"]  = x[0] if x else None
        r.append(y)
        logs.debug(f'{y}')

    logs.info("[小妖2] 开始导出数据...")
    f = write_excel(r)
    # f = "text.xml"
    logs.setpath(f)
    logs.info(f"[小妖2] 任务完成 {f}")